package com.sky.service.impl;

import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.Orders;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.ReportMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.LongStream;

/**
 * @ProjectName: sky-take-out
 * @Package: com.sky.service.impl
 * @ClassName: ReportServiceImpl
 * @Author: 王加其
 * @Date: 2023/4/29 15:29
 * @Version: 1.0
 */
@Service
public class ReportServiceImpl implements ReportService {
    @Autowired
    private OrderMapper ordersMapper;
    @Autowired
    private UserMapper userMapper;

    @Autowired
    private WorkspaceService workspaceService;

    /**
     * 营业额统计
     *
     * @param begin 开始日期
     * @param end   结束日期
     * @return
     */
    @Override
    public TurnoverReportVO turnoverStatistics(LocalDate begin, LocalDate end) {
        //1、根据起始日期，得到区间内的所有日期
        List<LocalDate> dateList = this.getDateList(begin, end);

        //2、查询每日的营业额，得到一个营业额List
        //方式一：循环刚才得到的日期列表，按照日期依次查询每天的营业额，放到一个List中（查询很多次）
        /*List<Double> turnoverList = new ArrayList<>();
        for (LocalDate date : dateList) {
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

            Map paramMap = new HashMap();
            paramMap.put("beginTime",beginTime);
            paramMap.put("endTime",endTime);
            paramMap.put("status",Orders.COMPLETED);

            //select sum(amount) from `orders` where status = 5  and order_time >= '2023-04-26 00:00:00' and order_time <= '2023-04-26 23:59:59';
            Double turnover = this.ordersMapper.sumAmountByParams(paramMap);
            turnover = turnover == null ? 0.0:turnover;
            turnoverList.add(turnover);
        }*/

        //方式二：分组统计营业额（只用查询一次）
        Map paramMap = new HashMap();
        paramMap.put("beginDate", begin);
        paramMap.put("endDate", end);
        paramMap.put("status", Orders.COMPLETED);

        //分组查询
        //    select DATE_FORMAT(order_time,'%Y-%m-%d') as `date`,sum(amount) amount from orders where status = 5
        //    and DATE_FORMAT(order_time,'%Y-%m-%d') >= '2023-04-22'
        //    and DATE_FORMAT(order_time,'%Y-%m-%d') <= '2023-04-28'
        //    group by `date`
        List<Map> turnoverMapList = this.ordersMapper.groupSumAmountByParams(paramMap);
        //将结果集转换成Map，方便后续根据日期取营业额
        Map<String, BigDecimal> turnoverMap = turnoverMapList.stream().collect(Collectors.toMap(m -> (String) m.get("date"), m -> (BigDecimal) m.get("amount")));
        List<BigDecimal> turnoverList = new ArrayList<>();
        for (LocalDate date : dateList) {
            BigDecimal amount = turnoverMap.get(date.toString());
            amount = ObjectUtils.isEmpty(amount) ? BigDecimal.valueOf(0.0) : amount;
            turnoverList.add(amount);
        }


        //3、将list转成逗号拼接的字符串
        String dateListStr = StringUtils.join(dateList, ",");
        dateListStr = dateList.stream().map(date -> date.toString()).collect(Collectors.joining(","));

        //4、组装TurnoverReportVO并返回
        String turnoverListStr = StringUtils.join(turnoverList, ",");
        return TurnoverReportVO.builder().dateList(dateListStr).turnoverList(turnoverListStr).build();
    }

    /**
     * 用户数据统计
     *
     * @param begin
     * @param end
     * @return
     */
    @Override
    public UserReportVO userStatistics(LocalDate begin, LocalDate end) {
        //1、根据起始日期，得到区间内的所有日期
        List<LocalDate> dateList = this.getDateList(begin, end);

        //2、查询每日的新增用户、截止当天用户总数，得到2个List
        //方式一：循环刚才得到的日期列表，按照日期依次查询每天新增用户数、截止当天的用户总数，分别放到2个List中（查询很多次）
        /*List<Integer> totalUserList = new ArrayList<>();
        List<Integer> newUserList = new ArrayList<>();
        for (LocalDate date : dateList) {
            //1、根据日期查询当天新增的用户数
            Map params = new HashMap();
            params.put("date",date);
            params.put("newUserflag",true);
            Integer newUserCount = this.userMapper.countUserByParams(params);
            newUserList.add(newUserCount);

            //2、根据日期查询截止到当天用户总数
            params.put("newUserflag",false);
            Integer totalUserCount = this.userMapper.countUserByParams(params);
            totalUserList.add(totalUserCount);
        }*/

        //方式二：分组统计用户数据（只用查询两次）
        Map params = new HashMap();
        params.put("beginDate", begin);
        params.put("endDate", end);
        List<Map> userMapList = this.userMapper.groupCountUserByParams(params);

        //分组查询每日新增用户数
        Map<String, Long> userMap = userMapList.stream().collect(Collectors.toMap(u -> (String) u.get("date"), u -> (Long) u.get("num")));

        //查询截止到开始日期之前的用户总数
        params.put("date", begin.minusDays(1));
        params.put("newUserflag", false);
        Integer beginUserCount = this.userMapper.countUserByParams(params);

        List<Integer> totalUserList = new ArrayList<>();
        List<Long> newUserList = new ArrayList<>();

        for (LocalDate date : dateList) {
            //当天新增用户数
            Long newUserCount = userMap.get(date.toString());
            newUserCount = newUserCount == null ? 0 : newUserCount;
            newUserList.add(newUserCount);

            //截止当天用户总数
            beginUserCount += newUserCount.intValue();
            totalUserList.add(beginUserCount);
        }

        //3、组装UserReportVO并返回
        return UserReportVO.builder().dateList(StringUtils.join(dateList, ","))
                .newUserList(StringUtils.join(newUserList, ","))
                .totalUserList(StringUtils.join(totalUserList, ",")).build();
    }

    /**
     * 订单数据统计
     *
     * @param begin
     * @param end
     * @return
     */
    @Override
    public OrderReportVO orderStatistics(LocalDate begin, LocalDate end) {
        //1、根据起始日期，得到区间内的所有日期
        List<LocalDate> dateList = this.getDateList(begin, end);

        //2、分组查询每日订单总数，放入到List中
        Map paramMap = new HashMap();
        paramMap.put("beginDate", begin);
        paramMap.put("endDate", end);
        List<Map> totalOrderMapList = this.ordersMapper.groupCountOrderByParams(paramMap);
        Map<String, Long> totalOrderMap = totalOrderMapList.stream().collect(Collectors.toMap(o -> (String) o.get("date"), o -> (Long) o.get("num")));

        //3、分组查询每日有效订单数，放入到List中
        paramMap.put("status", Orders.COMPLETED);
        List<Map> validOrderMapList = this.ordersMapper.groupCountOrderByParams(paramMap);
        Map<String, Long> validOrderMap = validOrderMapList.stream().collect(Collectors.toMap(o -> (String) o.get("date"), o -> (Long) o.get("num")));

        List<Long> orderCountList = new ArrayList<>();
        List<Long> validOrderCountList = new ArrayList<>();
        for (LocalDate date : dateList) {
            //获得当前日期的订单总数
            Long totalOrder = totalOrderMap.get(date.toString());
            totalOrder = totalOrder == null ? 0 : totalOrder;
            orderCountList.add(totalOrder);

            //获得当前日期的有效订单数
            Long validOrder = validOrderMap.get(date.toString());
            validOrder = validOrder == null ? 0 : validOrder;
            validOrderCountList.add(validOrder);
        }

        //4、统计区间内订单总数、有效订单总数
        Long totalOrderCount = orderCountList.stream().mapToLong(oc -> oc).sum();
        totalOrderCount = orderCountList.stream().reduce(Long::sum).get();
        totalOrderCount = orderCountList.stream().flatMapToLong(oc -> LongStream.of(oc)).sum();
        totalOrderCount = orderCountList.stream().mapToLong(oc -> oc).summaryStatistics().getSum();

        Long validOrderCount = validOrderCountList.stream().mapToLong(oc -> oc).sum();

        //5、计算订单完成率
        Double orderCompletionRate = totalOrderCount == 0 ? 0.0 : validOrderCount.doubleValue() / totalOrderCount;

        //6、组装OrderStatisticsVO并返回
        return OrderReportVO.builder().dateList(StringUtils.join(dateList, ","))
                .orderCountList(StringUtils.join(orderCountList, ","))
                .validOrderCountList(StringUtils.join(validOrderCountList, ","))
                .totalOrderCount(totalOrderCount.intValue())
                .validOrderCount(validOrderCount.intValue())
                .orderCompletionRate(orderCompletionRate).build();
    }

    /**
     * 销售排行统计
     *
     * @param begin
     * @param end
     * @return
     */
    @Override
    public SalesTop10ReportVO top10(LocalDate begin, LocalDate end) {
        //1、根据菜品或者套餐名称进行分组查询
        Map params = new HashMap();
        params.put("beginDate", begin);
        params.put("endDate", end);
        params.put("status", Orders.COMPLETED);
        List<GoodsSalesDTO> goodsSalesDTOList = this.ordersMapper.groupSumNumberByParams(params);

        //2、转换list，得到所有菜品或套餐名称
        String nameList = goodsSalesDTOList.stream().map(goods -> goods.getName()).collect(Collectors.joining(","));

        //3、转换list，得到所有菜品或套餐销量
        String numberList = goodsSalesDTOList.stream().map(goods -> goods.getNumber().toString()).collect(Collectors.joining(","));

        return SalesTop10ReportVO.builder().nameList(nameList).numberList(numberList).build();
    }

    /**
     * 运营数据导出
     *
     * @param response
     */
    @Override
    public void export(HttpServletResponse response) throws IOException, InvalidFormatException {
        //1、确定导出数据的时间范围
        LocalDate startDate = LocalDate.now().minusDays(30);
        LocalDate endDate = LocalDate.now().minusDays(1);

        //2、查询时间区间内的运营数据
        BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(startDate, LocalTime.MIN), LocalDateTime.of(endDate, LocalTime.MAX));

        //3、向excel中写入运营数据
        //exportStyle1(response,startDate, endDate, businessData);

        //exportStyle2(response,startDate, endDate, businessData);

        exportStyle3(response, startDate, endDate, businessData);
    }

    /**
     * 使用org.jxls进行基于excel模板的导出（模板根据批注读取配置）  poi  版本不能低于4.0
     * @param response
     * @param startDate
     * @param endDate
     * @param businessData
     * @throws IOException
     */
    private void exportStyle3(HttpServletResponse response, LocalDate startDate, LocalDate endDate, BusinessDataVO businessData) throws IOException {
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板3.xlsx");
        List<LocalDate> dateList = this.getDateList(startDate, endDate);
        List<BusinessDataVO> voList = new ArrayList<>();
        for (LocalDate date : dateList) {
            BusinessDataVO businessDataVO = this.workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
            businessDataVO.setDate(date.toString());
            voList.add(businessDataVO);
        }
        Context context = new Context();
        context.putVar("dataList" , voList);
        context.putVar("bd" , businessData);

        //5、写入到浏览器，关闭流
        response.setContentType("application/octet-stream");
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("运营数据报表1.xlsx", "UTF-8"));
        JxlsHelper.getInstance().processTemplate(in, new BufferedOutputStream(response.getOutputStream()), context);
    }

    /**
     * 使用net.sf.jxls.jxls-core进行基于excel模板的导出(poi 版本不能高于4.0)
     *
     * @param response
     * @param startDate
     * @param endDate
     * @param businessData
     * @return
     * @throws InvalidFormatException
     */
    private void exportStyle2(HttpServletResponse response, LocalDate startDate, LocalDate endDate, BusinessDataVO businessData) throws InvalidFormatException, IOException {
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板2.xlsx");
        List<LocalDate> dateList = this.getDateList(startDate, endDate);
        List<BusinessDataVO> voList = new ArrayList<>();
        for (LocalDate date : dateList) {
            BusinessDataVO businessDataVO = this.workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
            businessDataVO.setDate(date.toString());
            voList.add(businessDataVO);
        }

        //使用net.sf.jxls.jxls-core进行基于excel模板的导出（模板根据表达式读取配置） poi 版本不能高于4.0
        Map<String, Object> map = new HashMap<>();
        map.put("dataList", voList);
        map.put("bd", businessData);

        Workbook excel = new XLSTransformer().transformXLS(in, map);
        excel.write(response.getOutputStream());
    }

    /**
     * 原生POI导出
     * @param response
     * @param startDate
     * @param endDate
     * @param businessData
     * @throws IOException
     */
    private void exportStyle1(HttpServletResponse response, LocalDate startDate, LocalDate endDate, BusinessDataVO businessData) throws IOException {
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");
        XSSFWorkbook excel = new XSSFWorkbook(in);
        XSSFSheet sheet1 = excel.getSheetAt(0);

        sheet1.getRow(1).getCell(1).setCellValue("时间：" + startDate + "至" + endDate);
        //填充第4行的数据
        XSSFRow row4 = sheet1.getRow(3);
        row4.getCell(2).setCellValue(businessData.getTurnover());
        row4.getCell(4).setCellValue(businessData.getOrderCompletionRate());
        row4.getCell(6).setCellValue(businessData.getNewUsers());

        //填充第5行的数据
        XSSFRow row5 = sheet1.getRow(4);
        row5.getCell(2).setCellValue(businessData.getValidOrderCount());
        row5.getCell(4).setCellValue(businessData.getUnitPrice());


        //4、查询每日的明细数据，写入到excel中
        List<LocalDate> dateList = this.getDateList(startDate, endDate);
        int rownum = 0;
        for (LocalDate date : dateList) {
            businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
            XSSFRow row = sheet1.getRow(7 + rownum++);
            row.getCell(1).setCellValue(date.toString());
            row.getCell(2).setCellValue(businessData.getTurnover());
            row.getCell(3).setCellValue(businessData.getValidOrderCount());
            row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
            row.getCell(5).setCellValue(businessData.getUnitPrice());
            row.getCell(6).setCellValue(businessData.getNewUsers());
        }
        excel.write(response.getOutputStream());
    }

    /**
     * 根据起始日期，得到区间内的所有日期
     *
     * @param begin 开始日期
     * @param end   结束日期
     * @return 区间内的所有日期
     */
    private List<LocalDate> getDateList(LocalDate begin, LocalDate end) {
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);

        while (!begin.isEqual(end)) {
            begin = begin.plusDays(1);
            dateList.add(begin);
        }
        return dateList;
    }
}
